<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>数据准备 | Manor Farm Mysql</title>
    <meta name="generator" content="VuePress 1.7.1">
    <link rel="icon" href="/manor-farm-mysql/pgmanor_mysql.svg">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.css">
    <script language="javascript" type="text/javascript" src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="/manor-farm-mysql/js/MouseClickEffect.js"></script>
    <script language="javascript" type="text/javascript" src="/manor-farm-mysql/js/pgmanor-self.js"></script>
    <meta name="description" content="Mysql学习手册">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/manor-farm-mysql/assets/css/0.styles.7bfce8db.css" as="style"><link rel="preload" href="/manor-farm-mysql/assets/js/app.bbd54b43.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/3.33fa8ee0.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/1.e501e4e7.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/18.e8063fee.js" as="script"><link rel="preload" href="/manor-farm-mysql/assets/js/8.73e907b5.js" as="script"><link rel="prefetch" href="/manor-farm-mysql/assets/js/10.31e485b8.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/11.bd87e4cf.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/12.0fe1282f.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/13.f1de335a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/14.4a84a4ee.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/15.bc190a07.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/16.3f42a3ab.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/17.f1cad67c.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/19.068739ed.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/20.2b248747.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/21.4fd4cbc1.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/22.2683cc54.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/23.2e39bbe5.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/24.560a4164.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/25.26555323.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/26.8b7e1943.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/27.0f4ac9a1.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/28.f89ec4c0.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/29.71c751c3.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/30.ad576991.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/31.2746a45e.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/32.b62e21d8.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/33.ae9d6c1c.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/34.53969cd0.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/35.3e1a8a01.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/36.eb74300d.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/37.16568451.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/38.9a3bc894.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/39.7aab5a75.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/4.c231685d.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/40.cd44603a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/41.64d0c11a.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/42.3fb5d657.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/43.d4f19236.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/44.6abd5f31.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/45.eaf7b761.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/46.d891856e.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/5.0292b513.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/6.df2ba939.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/7.3b1acb14.js"><link rel="prefetch" href="/manor-farm-mysql/assets/js/9.5220a7da.js">
    <link rel="stylesheet" href="/manor-farm-mysql/assets/css/0.styles.7bfce8db.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-2d5f533b><div data-v-2d5f533b><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-2d5f533b data-v-2d5f533b><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-59a94c5a data-v-2d5f533b data-v-2d5f533b><h3 class="title" style="display:none;" data-v-59a94c5a data-v-59a94c5a>Manor Farm Mysql</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-59a94c5a data-v-59a94c5a><input type="password" value="" data-v-59a94c5a> <span data-v-59a94c5a>已加密!</span> <button data-v-59a94c5a>OK</button></label> <div class="footer" style="display:none;" data-v-59a94c5a data-v-59a94c5a><span data-v-59a94c5a><i class="iconfont reco-eye" data-v-59a94c5a></i> <a target="blank" href="https://pgmanor.gitee.io/blog/" data-v-59a94c5a>密钥</a></span> <span data-v-59a94c5a><i class="iconfont reco-theme" data-v-59a94c5a></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-59a94c5a>vuePress-theme-reco</a></span> <span data-v-59a94c5a><i class="iconfont reco-copyright" data-v-59a94c5a></i> <a data-v-59a94c5a><span data-v-59a94c5a>pgmanor</span>
  
<!---->
2021
</a></span></div></div> <div class="hide" data-v-2d5f533b><header class="navbar" data-v-2d5f533b><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/manor-farm-mysql/" class="home-link router-link-active"><img src="/manor-farm-mysql/pgmanor_mysql.svg" alt="Manor Farm Mysql" class="logo"> <span class="site-name">Manor Farm Mysql</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/manor-farm-mysql/zh/guide/" class="nav-link router-link-active"><i class="iconfont reco-api"></i>
  指南
</a></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/messageBoard.html" class="nav-link"><i class="iconfont reco-suggestion"></i>
  留言版
</a></div><div class="nav-item"><a href="https://pgmanor.gitee.io/blog/2021/01/10/secret-farm-mysql/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-eye"></i>
  文章密钥
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-qq"></i>
      联系
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>QQ在线</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://wpa.qq.com/msgrd?v=3&amp;uin=2307546059&amp;site=qq&amp;menu=yes" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>其他方式</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/manor-farm-mysql/zh/fix/contactUs.html" class="nav-link"><i class="iconfont undefined"></i>
  更多
</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-other"></i>
      友链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://pgmanor.gitee.io/blog/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://blog.csdn.net/u011622109" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-csd"></i>
  csdn
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://www.iworkh.com" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  iworkh
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>Vue</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://cn.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  Vue
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://v1.vuepress.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  VuePress
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://vuepress-theme-reco.recoluan.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  vuepress-theme-reco
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li></ul></div></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/donate.html" class="nav-link"><i class="iconfont reco-Coding"></i>
  捐赠
</a></div><div class="nav-item"><a href="https://gitee.com/pgmanor/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-2d5f533b></div> <aside class="sidebar" data-v-2d5f533b><div class="personal-info-wrapper" data-v-ca798c94 data-v-2d5f533b><img src="/manor-farm-mysql/pgmanor_mysql.svg" alt="author-avatar" class="personal-img" data-v-ca798c94> <h3 class="name" data-v-ca798c94>
    pgmanor
  </h3> <div class="num" data-v-ca798c94><div data-v-ca798c94><h3 data-v-ca798c94>30</h3> <h6 data-v-ca798c94>文章</h6></div> <div data-v-ca798c94><h3 data-v-ca798c94>26</h3> <h6 data-v-ca798c94>标签</h6></div></div> <hr data-v-ca798c94></div> <nav class="nav-links"><div class="nav-item"><a href="/manor-farm-mysql/zh/guide/" class="nav-link router-link-active"><i class="iconfont reco-api"></i>
  指南
</a></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/messageBoard.html" class="nav-link"><i class="iconfont reco-suggestion"></i>
  留言版
</a></div><div class="nav-item"><a href="https://pgmanor.gitee.io/blog/2021/01/10/secret-farm-mysql/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-eye"></i>
  文章密钥
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-qq"></i>
      联系
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>QQ在线</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://wpa.qq.com/msgrd?v=3&amp;uin=2307546059&amp;site=qq&amp;menu=yes" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>其他方式</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/manor-farm-mysql/zh/fix/contactUs.html" class="nav-link"><i class="iconfont undefined"></i>
  更多
</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-other"></i>
      友链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://pgmanor.gitee.io/blog/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  pgmanor
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://blog.csdn.net/u011622109" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-csd"></i>
  csdn
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://www.iworkh.com" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  iworkh
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li><li class="dropdown-item"><h4>Vue</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="https://cn.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  Vue
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://v1.vuepress.vuejs.org/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  VuePress
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-subitem"><a href="https://vuepress-theme-reco.recoluan.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont undefined"></i>
  vuepress-theme-reco
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></li></ul></div></div><div class="nav-item"><a href="/manor-farm-mysql/zh/fix/donate.html" class="nav-link"><i class="iconfont reco-Coding"></i>
  捐赠
</a></div><div class="nav-item"><a href="https://gitee.com/pgmanor/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-mayun"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></div> <!----></nav> <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>初入尘世</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>千锤百炼</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/manor-farm-mysql/zh/guide/advanced/a-01-introduction.html" class="sidebar-link">本章介绍</a></li><li><a href="/manor-farm-mysql/zh/guide/advanced/a-02-prepare-data.html" aria-current="page" class="active sidebar-link">数据准备</a></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><a href="/manor-farm-mysql/zh/guide/" class="sidebar-heading clickable router-link-active"><span>原理</span> <span class="arrow right"></span></a> <!----></section></li><li><section class="sidebar-group collapsable is-sub-group depth-1"><a href="/manor-farm-mysql/zh/guide/" class="sidebar-heading clickable router-link-active"><span>优化</span> <span class="arrow right"></span></a> <!----></section></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>百战成神</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-59a94c5a data-v-2d5f533b><h3 class="title" style="display:none;" data-v-59a94c5a data-v-59a94c5a>数据准备</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-59a94c5a data-v-59a94c5a><input type="password" value="" data-v-59a94c5a> <span data-v-59a94c5a>已加密!</span> <button data-v-59a94c5a>OK</button></label> <div class="footer" style="display:none;" data-v-59a94c5a data-v-59a94c5a><span data-v-59a94c5a><i class="iconfont reco-eye" data-v-59a94c5a></i> <a target="blank" href="https://pgmanor.gitee.io/blog/" data-v-59a94c5a>密钥</a></span> <span data-v-59a94c5a><i class="iconfont reco-theme" data-v-59a94c5a></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-59a94c5a>vuePress-theme-reco</a></span> <span data-v-59a94c5a><i class="iconfont reco-copyright" data-v-59a94c5a></i> <a data-v-59a94c5a><span data-v-59a94c5a>pgmanor</span>
  
<!---->
2021
</a></span></div></div> <div data-v-2d5f533b><main class="page"><div class="page-title" style="display:none;"><h1 class="title">数据准备</h1> <div data-v-3b7f5bdf><i class="iconfont reco-account" data-v-3b7f5bdf><span data-v-3b7f5bdf>pgmanor</span></i> <i class="iconfont reco-date" data-v-3b7f5bdf><span data-v-3b7f5bdf>2021-01-02</span></i> <i class="iconfont reco-eye" data-v-3b7f5bdf><span id="/manor-farm-mysql/zh/guide/advanced/a-02-prepare-data.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-3b7f5bdf><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-3b7f5bdf><span class="tag-item" data-v-3b7f5bdf>数据</span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><h2 id="_1-数据准备">1.数据准备</h2> <p>后面sql会使用官网提供的测试数据来演示，所以我们先准备下测试数据。</p> <h3 id="_1-1-employees">1-1.employees</h3> <ul><li><a href="https://dev.mysql.com/doc/employee/en/employees-installation.html" target="_blank" rel="noopener noreferrer">官网employees数据文档<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li><a href="https://github.com/datacharmer/test_db" target="_blank" rel="noopener noreferrer">官网employees数据github<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <p><strong>步骤</strong></p> <ol><li>先github下载测试数据</li> <li>客户端连接mysql</li> <li>导入数据</li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 登录</span>
mysql <span class="token operator">-</span>u pgmanor <span class="token operator">-</span>p
<span class="token comment">-- 输入密码 xxx</span>
<span class="token comment">-- 导入数据</span>
source employees<span class="token punctuation">.</span><span class="token keyword">sql</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_1-2-sakila">1-2.sakila</h3> <p>sakila数据集是MySQL官方提供的一个学习MySQL的很好的素材</p> <ul><li><a href="https://dev.mysql.com/doc/sakila/en/sakila-installation.html" target="_blank" rel="noopener noreferrer">官网sakila数据文档<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li><a href="https://dev.mysql.com/doc/index-other.html" target="_blank" rel="noopener noreferrer">sakila测试数据下载<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <p><strong>步骤</strong></p> <ol><li>选择mysql对应版本的下载测试数据</li> <li>客户端连接mysql</li> <li>导入数据</li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 登录</span>
mysql <span class="token operator">-</span>u pgmanor <span class="token operator">-</span>p
<span class="token comment">-- 输入密码 xxx</span>
<span class="token comment">-- 导入数据</span>
source sakila<span class="token operator">-</span><span class="token keyword">data</span><span class="token punctuation">.</span><span class="token keyword">sql</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_1-3-tpc系列">1-3.tpc系列</h3> <p>还有tpc系列的，有兴趣的可以自己补充下</p> <ul><li><a href="http://www.tpc.org/" target="_blank" rel="noopener noreferrer">tpc官网<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <p>TPC标准它只提供一个规范,就是用文字描述一种标准的业务场景,但具体怎么实现TPC组织不管,你自己写代码实现去.当然有些软件产品会提供这些功能</p> <p>刚开始制定了TPC-A,TPC-B标准,但现在基本废弃了。后面又出现了TPC-C,TPC-D,TPC-E,TPC-H.TPC-DS等。</p> <ul><li>TPC-C,TPC-E是针对<code>OLTP</code>系统的，TPC-E是TPC-C的改进版.</li> <li>TPC-D,TPC-H,TPC-DS是针对<code>OLAP</code>系统的，TPC-D基本上没用了,现在主要用TPC-H和TPC-DS.</li></ul> <p><strong>TPC-C</strong></p> <blockquote><p>TPC-C标准就是定义这样一种业务场景:
假设有一个大型商品批发商，它拥有若干个分布在不同区域的商品库；
每个仓库负责为10个销售点供货；每个销售点为3000个客户提供服务；
每个客户平均一个订单有10项产品;所有订单中约1%的产品在其直接所属的仓库中没有存货，需要由其他区域的仓库来供货</p></blockquote> <p><strong>TPC-DS</strong></p> <blockquote><p>新兴的数据仓库开始采用新的模型，如星型模型、雪花模型。TPC-H已经不能精准反映当今数据库系统的真实性能。
为此，TPC组织推出了新一代的面向决策应用的TPC-DS 基准。</p></blockquote> <p><strong>OLAP和OLTP</strong></p> <ul><li>OLTP: 联机事务处理系统</li> <li>OLAP: 联机分析处理系统</li></ul> <blockquote><p>更多细节可以查看<a href="https://baike.baidu.com/item/OLTP" target="_blank" rel="noopener noreferrer">百度百科<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></blockquote> <h2 id="_2-employees">2.employees</h2> <p>该数据库中有6张关联，记录的数据为某一公司9个部门所有员工的薪资（包括离职员工）</p> <h3 id="_2-1-导入数据">2-1.导入数据</h3> <ol><li>先下载employees测试数据<a href="https://github.com/datacharmer/test_db" target="_blank" rel="noopener noreferrer">github下载地址<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>，
<a href="https://launchpad.net/test-db/employees-db-1" target="_blank" rel="noopener noreferrer">launchpad下载地址<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ol> <blockquote><p>官网下载实在太慢，分享个<a href="https://pan.baidu.com/s/1t_NhWjy2DFfemtZFp9mKUg" target="_blank" rel="noopener noreferrer">百度网盘下载地址<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a> 提取码：pg66</p></blockquote> <ol start="2"><li>连接数据库</li> <li>导入数据</li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 连接</span>
mysql <span class="token operator">-</span>u pgmanor <span class="token operator">-</span>p
<span class="token comment">-- 导入数据</span>
source D:<span class="token operator">/</span>tmp_backup<span class="token operator">/</span>test_db<span class="token operator">-</span>master<span class="token operator">/</span>employees<span class="token punctuation">.</span><span class="token keyword">sql</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="_3-2-er图">3-2.er图</h3> <p>表和视图共以下几个</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> <span class="token keyword">FULL</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>Tables_in_employees</th> <th>Table_type</th></tr></thead> <tbody><tr><td>departments</td> <td>BASE TABLE</td></tr> <tr><td>dept_emp</td> <td>BASE TABLE</td></tr> <tr><td>dept_manager</td> <td>BASE TABLE</td></tr> <tr><td>employees</td> <td>BASE TABLE</td></tr> <tr><td>salaries</td> <td>BASE TABLE</td></tr> <tr><td>titles</td> <td>BASE TABLE</td></tr></tbody></table> <p><img src="/manor-farm-mysql/images/advanced/optimization/employees-er.png" alt="employees-er"></p> <h3 id="_3-3-具体表">3-3.具体表</h3> <h4 id="_2-3-1-departments">2-3-1.departments</h4> <p>记录的是9个部门的部门编号和部门名称</p> <ul><li>dept_no 部门编号</li> <li>dept_name	部门名称</li></ul> <h4 id="_2-3-2-dept-emp">2-3-2.dept_emp</h4> <p>部门员工数据，员工id和部门id，其实时间和结束时间（注：9999的意思就是仍在职），数据量<code>331603</code></p> <ul><li>emp_no 员工编号</li> <li>dept_no 部门编号</li> <li>from_date 开始日期</li> <li>to_date 终止日期</li></ul> <h4 id="_2-3-3-dept-manger">2-3-3.dept_manger</h4> <p>同第二张表结构差不多，每个部门的每个经理的任职时期，总共就24个人，每个部门至少有过两个经理。</p> <ul><li>dept_no 部门编号</li> <li>emp_no 员工编号</li> <li>from_date 开始日期</li> <li>to_date 终止日期</li></ul> <h4 id="_2-3-4-employees">2-3-4.employees</h4> <p>员工信息表，emp_no是唯一键值。数据量<code>300024</code></p> <ul><li>emp_no 员工编号</li> <li>birth_date 生日</li> <li>first_name 名</li> <li>last_name 姓</li> <li>gender 性别</li> <li>hire_date 雇佣日期</li></ul> <h4 id="_2-3-5-salaries">2-3-5.salaries</h4> <p>记录每个员工每段时期的薪资！数据量<code>2844047</code></p> <ul><li>emp_no 员工编号</li> <li>salary 薪水</li> <li>from_date 开始日期</li> <li>to_date 终止日期</li></ul> <h4 id="_2-3-6-title">2-3-6.title</h4> <p>记录每个员工每段时期的职位名称！但请注意，周期与第五张表是不同的，因为在同一职位上你也是会涨工资的嘛。数据量<code>443308</code></p> <ul><li>emp_no 员工编号</li> <li>title 职位名称</li> <li>from_date 开始日期</li> <li>to_date 终止日期</li></ul> <h2 id="_3-sakila">3.sakila</h2> <p>我们以sakila数据来演示，所以我们先看下<strong>Sakila数据库业务与结构</strong></p> <ul><li>actor（演员）</li> <li>film（电影）</li> <li>category（类别）</li> <li>inventory（库存）</li> <li>customer（顾客）</li> <li>rental（租赁信息）</li> <li>等 (共16张表)</li></ul> <h3 id="_3-1-导入数据">3-1.导入数据</h3> <ol><li>先下载<a href="https://dev.mysql.com/doc/index-other.html" target="_blank" rel="noopener noreferrer">sakila测试数据下载<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>，解压后共三个文件</li></ol> <ul><li><code>sakila-schema.sql</code> 是数据库的建表语句等</li> <li><code>sakila-data.sql</code> 是数据</li> <li><code>sakila.mwb</code> 是mysql Workbench数据结构模型文件。（可忽略）</li></ul> <ol start="2"><li>连接数据库</li> <li>导入数据</li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 连接</span>
mysql <span class="token operator">-</span>u pgmanor <span class="token operator">-</span>p
<span class="token comment">-- 导入schema</span>
source D:<span class="token operator">/</span>tmp_backup<span class="token operator">/</span>sakila<span class="token operator">-</span>db<span class="token operator">/</span>sakila<span class="token operator">-</span><span class="token keyword">schema</span><span class="token punctuation">.</span><span class="token keyword">sql</span>
<span class="token comment">-- 导入data</span>
source D:<span class="token operator">/</span>tmp_backup<span class="token operator">/</span>sakila<span class="token operator">-</span>db<span class="token operator">/</span>sakila<span class="token operator">-</span><span class="token keyword">data</span><span class="token punctuation">.</span><span class="token keyword">sql</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h3 id="_3-2-er图-2">3-2.ER图</h3> <p><strong>表和视图共以下几个</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SHOW</span> <span class="token keyword">FULL</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><table><thead><tr><th>Tables_in_sakila</th> <th>Table_type</th> <th>描述</th></tr></thead> <tbody><tr><td>actor</td> <td>BASE TABLE</td> <td>演员表</td></tr> <tr><td>actor_info</td> <td>VIEW</td> <td>演员视图</td></tr> <tr><td>address</td> <td>BASE TABLE</td> <td>地址表</td></tr> <tr><td>category</td> <td>BASE TABLE</td> <td>电影类别表</td></tr> <tr><td>city</td> <td>BASE TABLE</td> <td>城市表</td></tr> <tr><td>country</td> <td>BASE TABLE</td> <td>国家表</td></tr> <tr><td>customer</td> <td>BASE TABLE</td> <td>客户表</td></tr> <tr><td>customer_list</td> <td>VIEW</td> <td>客户表视图</td></tr> <tr><td>film</td> <td>BASE TABLE</td> <td>电影表</td></tr> <tr><td>film_actor</td> <td>BASE TABLE</td> <td>电影和演员中间对应表</td></tr> <tr><td>film_category</td> <td>BASE TABLE</td> <td>电影和类别的中间对应表</td></tr> <tr><td>film_list</td> <td>VIEW</td> <td>电影表视图</td></tr> <tr><td>film_text</td> <td>BASE TABLE</td> <td>只包含title和description两列的电影表子表，用以快速检索</td></tr> <tr><td>inventory</td> <td>BASE TABLE</td> <td>库存表</td></tr> <tr><td>language</td> <td>BASE TABLE</td> <td>语言表</td></tr> <tr><td>nicer_but_slower_film_list</td> <td>VIEW</td> <td></td></tr> <tr><td>payment</td> <td>BASE TABLE</td> <td>付款表</td></tr> <tr><td>rental</td> <td>BASE TABLE</td> <td>租金表</td></tr> <tr><td>sales_by_film_category</td> <td>VIEW</td> <td>根据电影类别的票数视图</td></tr> <tr><td>sales_by_store</td> <td>VIEW</td> <td>根据商店的票数视图</td></tr> <tr><td>staff</td> <td>BASE TABLE</td> <td>工作人员表</td></tr> <tr><td>staff_list</td> <td>VIEW</td> <td>工作人员视图</td></tr> <tr><td>store</td> <td>BASE TABLE</td> <td>商店表</td></tr></tbody></table> <p><img src="/manor-farm-mysql/images/advanced/optimization/sakila-er.png" alt="sakila-er"></p> <h3 id="_3-3-具体表-2">3-3.具体表</h3> <h4 id="_3-3-1-演员表actor">3-3-1.演员表actor</h4> <p>演员表列出了所有演员的信息。演员表和电影表之间是多对多的关系，通过film_actor表建立关系</p> <ul><li>actor_id: 代理主键用于唯一标识表中的每个演员</li> <li>first_name: 演员的名字</li> <li>last_name: 演员的姓氏</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-2-地址表address">3-3-2.地址表address</h4> <p>地址表包含客户、员工和商店的地址信息。地址表的主键出现在顾客、 员工、和存储表的外键 。</p> <ul><li>address_id: 代理主键用于唯一标识表中的每个地址</li> <li>address: 地址的第一行</li> <li>address2: 一个可选的第二行地址</li> <li>district: 该地区的所属地区，这可以是国家，省，县等</li> <li>city_id: 指向城市表的外键</li> <li>postal_code: 邮政编码</li> <li>phone: 地址的电话号码</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-3-分类表category">3-3-3.分类表category</h4> <p>类别表列出了可以分配到一个电影类别。分类和电影是多对多的关系，通过表film_category建立关系</p> <ul><li>category_id: 代理主键用于唯一标识表中的每个类别</li> <li>name: 类别名称</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-4-城市表city">3-3-4.城市表city</h4> <p>城市表包含的城市名单。城市表使用外键来标示国家；在地址表中被作为外键来使用。</p> <ul><li>city_id: 代理主键用于唯一标识表中的每个城市</li> <li>city: 城市的名字</li> <li>country_id: 外键，用于标示城市所属的国家</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-5-国家表country">3-3-5.国家表country</h4> <p>国家表中包含的国家名单。国家表是指在城市表的外键 。</p> <ul><li>country_id: 代理主键用于唯一标识表中的每个国家</li> <li>country: 国家的名称</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-6-客户表customer">3-3-6.客户表customer</h4> <p>客户表包含了所有客户的列表 。客户表在支付表和租金表被作为外键使用；客户表使用外键来表示地址和存储。</p> <ul><li>customer_id: 代理主键用于唯一标识表中的每个客户</li> <li>store_id: 一个外键，确定客户所属的store。</li> <li>first_name: 客户的名字</li> <li>last_name: 客户的姓氏</li> <li>email: 客户的电子邮件地址</li> <li>address_id: 使用在地址 表的外键来确定客户的地址</li> <li>active: 表示客户是否是活跃的客户</li> <li>create_date: 顾客被添加到系统中的日期。使用 INSERT 触发器自动设置。</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <div class="custom-block tip"><p class="custom-block-title">说明</p> <p><strong>active:</strong> 此设置为<code>FALSE</code>作为替代客户彻底删除。大多数查询应该有一个 WHERE active = TRUE 字句。</p> <p><strong>store_id:</strong> 此处的客户不仅限于只由这家商店出租，而是包括客户常常去逛的商店</p></div> <h4 id="_3-3-7-电影表film">3-3-7.电影表film</h4> <p>电影表是一个可能在商店库存的所有影片名单。每部影片的拷贝的实际库存信息保存在库存表。电影表指使用外键来标示语言表；在film_category、film_actor和库存表中作为外键使用。</p> <ul><li>film_id: 代理主键用于唯一标识表中的每个电影</li> <li>title: 影片的标题</li> <li>description: 一个简短的描述或电影的情节摘要</li> <li>release_year: 电影发行的年份</li> <li>language_id: 使用外键来标示语言</li> <li>original_language_id: 电影的原始语音。使用外键来标示语言</li> <li>rental_duration: 租赁期限的长短，以天作为单位</li> <li>rental_rate: 指定的期限内电影的租金</li> <li>length: 影片的长度，以分钟为单位。</li> <li>replacement_cost: 如果电影未被归还或损坏状态向客户收取的款项</li> <li>rating: 分配给电影评级。可以是 G， PG，PG - 13 ， R 或NC - 17</li> <li>special_features: 包括DVD上常见的特殊功能的列表</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <p>特殊功能包括零个或多个评论、删剪片段、幕后。</p> <h4 id="_3-3-8-film-actor表">3-3-8.film_actor表</h4> <p>film_actor表是用来支持许多电影和演员之间的多对多关系 。对于每一个给定的电影演员，将有film_actor表中列出的演员和电影中的一个行 。</p> <p>film_actor表指的是使用外键的电影和演员表。</p> <ul><li>actor_id: 用于识别演员的外键</li> <li>film_id: 用于识别电影的外键</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-9-film-category表">3-3-9.film_category表</h4> <p>film_category表是用来支持许多电影和类别之间的多对多关系 。应用于电影的每个类别中，将有film_category表中列出的类别和电影中的一个行。</p> <p>film_category表是指使用外键 的 电影 和类别表 。</p> <ul><li>film_id: 用于识别电影的外键</li> <li>category_id: 用于识别类别的外键</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-10-film-text表">3-3-10.film_text表</h4> <p>film_text表是Sakila样例数据库唯一使用MyISAM存储引擎的表。此表提供允许全文搜索电影表中列出的影片的标题和描述。
film_text表包含的film_id，标题和描述的列电影表，保存的内容与电影表上的内容同步（指电影表的插入、更新和删除操作）</p> <ul><li>MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持</li> <li>MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。</li></ul> <p><strong>字段</strong></p> <ul><li>film_id: 代理主键用于唯一标识表中的每个电影</li> <li>title: 影片的标题</li> <li>description: 一个简短的描述或电影的情节摘要</li></ul> <p>注意：film_text表的内容不应该直接修改。所有的变更来自于电影表 。</p> <h4 id="_3-3-11-库存表inventory">3-3-11.库存表inventory</h4> <p>库存表的一行为存放在一个给定的商店里的一个给定的电影的copy副本。库存表是使用外键来识别电影和存储；在出租表中使用外键来识别库存。</p> <ul><li>inventory_id: 主键用于唯一标识每个项目在库存</li> <li>film_id: 使用外键来识别电影</li> <li>store_id: 使用外键来识别物品所在的商店</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-12-语言表language">3-3-12.语言表language</h4> <p>语言表是一个查找表，列出可能使用的语言，电影可以有自己的语言和原始语言值 。</p> <p>语言表在电影表中被作为外键来使用。</p> <ul><li>language_id: 代理主键用于唯一标识每一种语言</li> <li>name: 语言的英文名称</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-13-付款表-payment">3-3-13.付款表 payment</h4> <p>付款表记录每个客户的付款，如支付的金额和租金的资料。</p> <p>付款表使用外键来表示客户、出租、和工作人员。</p> <ul><li>payment_id: 代理主键用于唯一标识每个付款</li> <li>customer_id: 使用外键来标识付款的客户</li> <li>staff_id: 工作人员，负责处理支付 。使用外键来标识</li> <li>rental_id: 租借ID, 外键，参照rental表</li> <li>amount: 付款金额</li> <li>payment_date: 处理付款的日期</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-14-租金表-rental">3-3-14.租金表 rental</h4> <p>租借表的一行表示每个inventory的租借客户、租借时间、归还时间。</p> <p>租借表是使用外键来标识库存 ，顾客 和工作人员；在支付表中使用了外键来标识租金 。</p> <ul><li>rental_id: 代理主键唯一标识的租金</li> <li>rental_date: 该项目租用的日期和时间</li> <li>inventory_id: 该项目被租用</li> <li>customer_id: 租用该项目的客户</li> <li>return_date: 归还日期</li> <li>staff_id: 处理该项业务的工作人员</li> <li>last_update: 该行已创建或最近更新的时间</li></ul> <h4 id="_3-3-15-工作人员表-staff">3-3-15.工作人员表 staff</h4> <p>工作人员表列出了所有的工作人员，包括电子邮件地址，登录信息和图片信息 。</p> <p>工作人员表是指使用外键来标识存储和地址表；在出租、支付和商店表中作为外键。</p> <ul><li>staff_id: 代理主键唯一标识的工作人员</li> <li>first_name: 工作人员的名字</li> <li>last_name: 工作人员的姓氏</li> <li>address_id: 工作人员的地址在地址表的外键</li> <li>picture: 工作人员的照片，使用了 BLOB属性</li> <li>email: 工作人员的电子邮件地址</li> <li>store_id: 工作人员所在的商店，用外键标识</li> <li>active: 是否是活跃的工作人员。</li> <li>username: 用户名，由工作人员用来访问租赁系统</li> <li>password: 工作人员访问租赁系统所使用的密码。使用了 SHA1 函数</li> <li>last_update: 该行已创建或最近更新的时间</li> <li>active: 是否有效，删除时设置为False</li></ul> <h4 id="_3-3-16-商店表-store">3-3-16.商店表 store</h4> <p>store表列出了系统中的所有商店 。</p> <p>store使用外键来标识工作人员和地址；在员工、客户、库存表被作为外键使用。</p> <ul><li>store_id: 代理主键唯一标识的商店</li> <li>manager_staff_id: 使用外键来标识这家商店的经理</li> <li>address_id: 使用外键来确定这家店的地址</li> <li>last_update: 该行已创建或最近更新的时间</li></ul></div> <footer class="page-edit" style="display:none;"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2021-1-12 10:56:15 PM</span></div></footer> <!----> <!----> <!----></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div><!----><!----><APlayer audio="" fixed="true" mini="true" theme="#f9bcdd" loop="loop" order="random" preload="auto" volume="0.1" mutex="true" lrc-type="0" list-folded="true" list-max-height="250" storage-name="vuepress-plugin-meting" id="aplayer-fixed"></APlayer></div></div>
    <script src="/manor-farm-mysql/assets/js/app.bbd54b43.js" defer></script><script src="/manor-farm-mysql/assets/js/3.33fa8ee0.js" defer></script><script src="/manor-farm-mysql/assets/js/1.e501e4e7.js" defer></script><script src="/manor-farm-mysql/assets/js/18.e8063fee.js" defer></script><script src="/manor-farm-mysql/assets/js/8.73e907b5.js" defer></script>
  </body>
</html>
